﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace _1242026_1242133.DAO
{
    class SqlSever
    {
        private SqlConnection connection; //bien de ket noi
        private SqlCommand command; // ben de thuc thi truy van
       // private SqlDataReader reader; // bien de doc bang du lieu
        private SqlDataAdapter adapter;// lay bang du lieu
      //  private string connectionstring = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DoanKhachSan.mdf;Integrated Security=True;User Instance=True";
        private string connectionstring = @"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\HOAN CHINH\HOC KY 1\Nhap mon cn pm\Nhung Bai Nop\1242133_1242026\1242026_1242133\DoanKhachSan.mdf;Integrated Security=True;User Instance=True";
        // public SqlSever()
        //{
        // code thuc thi
        //connectionstring = @"Data Source=THANG-PC\SQLEXPRESS;Initial Catalog=QLBANHANG;Integrated Security=True";
        // }
        private void Openconnection() // mo ket noi
        {
            connection = new SqlConnection(connectionstring);
            if (connection.State == ConnectionState.Closed)
                connection.Open();
        }

        public DataTable ExecuteCommandText(string cmdText)
        {
            try
            {
                Openconnection();
                command = new SqlCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = cmdText;
                command.Connection = connection;
                adapter = new SqlDataAdapter(command);
                DataTable result = new DataTable();
                adapter.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi Không Truy Vấn Được Dữ Liệu" + ex.Message);
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
        }
        public bool ExecuteNonQuery(string cmdText)
        {
            int Number = 0;
            try
            {
                Openconnection(); // lay gia tri cua  cua bien conection la chuoi ket noi
                command = new SqlCommand(); // tao doi tuong commad
                command.CommandType = CommandType.Text; // kieu cua truy van
                command.CommandText = cmdText; // gan chuoi truy van
                command.Connection = connection; // vi bien conection trong ham Openconnection() nen gan lai de khong can truyen ket noi vao ham excutenoquery()
                Number = command.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception("Truy Vấn Gặp Lỗi " + ex.Message);
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            if (Number <= 0)
                return false;
            return true;
        }

        public DataTable ExecuteStore(string TenStore, SqlParameter[] ThamSo)
        {
            try
            {
                Openconnection(); // lay gia tri cua  cua bien conection la chuoi ket noi
                command = new SqlCommand(); // tao doi tuong commad
                command.CommandType = CommandType.StoredProcedure; // kieu cua truy van
                command.CommandText = TenStore; // gan chuoi truy van
                command.Connection = connection; // ga'n lai de mo ket noi

                SqlDataAdapter sqlDA = new SqlDataAdapter();
                adapter = new SqlDataAdapter(command);
                DataTable result = new DataTable();
                for (int i = 0; i < ThamSo.Length; i++)
                {
                    command.Parameters.Add(ThamSo[i]);
                }
                adapter.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("Truy Van Gap loi" + ex.Message);
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
        }

        public DataTable ExecuteStore(string TenStore)
        {
            try
            {
                Openconnection(); // lay gia tri cua  cua bien conection la chuoi ket noi
                command = new SqlCommand(); // tao doi tuong commad
                command.CommandType = CommandType.StoredProcedure; // kieu cua truy van
                command.CommandText = TenStore; // gan chuoi truy van
                command.Connection = connection; // ga'n lai de mo ket noi

                SqlDataAdapter sqlDA = new SqlDataAdapter();
                adapter = new SqlDataAdapter(command);
                DataTable result = new DataTable();
                adapter.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("Truy Van Gap loi" + ex.Message);
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
        }
        public DataTable ExecuteStoredProcedure(int ma)
        {
            try
            {
                Openconnection();
                command = new SqlCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "sp_KhachHang";
                command.Parameters.Add(new SqlParameter("@makh", ma));
                command.Connection = connection;
                adapter = new SqlDataAdapter(command);
                DataTable result = new DataTable();
                adapter.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("Truy Van Gap loi" + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
